from fastapi import APIRouter, Query, UploadFile, File, HTTPException, Form
from fastapi.responses import JSONResponse, StreamingResponse, RedirectResponse
import sqlite3
import mysql.connector
import os
import hashlib
import datetime
import shutil
import json
from typing import List, Optional, Dict, Any, Tuple
from utils import nas_sqlite_dirdb_get, get_mysql_conn, CONFIG, nas_add_folder_create_v1, nas_rename_folder, nas_move_folder, nas_copy_folder, nas_delete, RECYBIN_PATH, ROOTPATH, get_best_edgesrv


# 创建文件夹操作的路由
folder_router = APIRouter()

# 通用数据库查询函数
def folder_db_query(corpid: str, path: Optional[str] = None, sql: str = "", params: tuple = (), count_sql: str = "") -> Tuple[List, int, Optional[str]]:
    """
    通用数据库查询函数
    
    Args:
        corpid: 企业ID
        path: 路径参数
        sql: SQL查询语句
        params: SQL参数
        count_sql: 总数查询SQL
    
    Returns:
        Tuple[查询结果列表, 总数, 错误信息]
    """
    try:
        # 统一使用路径参数连接数据库
        sqlite_conn = nas_sqlite_dirdb_get(corpid, path)
            
        if not sqlite_conn:
            return [], 0, "数据库连接失败"
        
        cursor = sqlite_conn.cursor()
        
        # 查询总数（所有操作都需要总数）
        totalnum = 0
        if count_sql:
            cursor.execute(count_sql)
            totalnum = cursor.fetchone()[0]
        else:
            # 如果没有提供count_sql，尝试通过修改原sql来获取总数
            count_sql_base = sql.lower()
            select_pos = count_sql_base.find("select")
            from_pos = count_sql_base.find("from")
            if select_pos >= 0 and from_pos > select_pos:
                temp_count_sql = f"SELECT COUNT(*) {sql[from_pos:]}"
                # 移除LIMIT和OFFSET子句
                temp_count_sql = temp_count_sql.replace("LIMIT ? OFFSET ?", "").strip()
                cursor.execute(temp_count_sql)
                totalnum = cursor.fetchone()[0]
        
        # 执行主查询
        cursor.execute(sql, params)
        results = cursor.fetchall()
            
        sqlite_conn.close()
        return results, totalnum, None
        
    except Exception as e:
        print(f"数据库查询失败: {e}")
        try:
            sqlite_conn.close()
        except:
            pass
        return [], 0, f"内部错误: {str(e)}"

# 文件夹操作接口

# 获取根目录接口
@folder_router.get("/v1/oss/folder")
async def folder_operations(
    action: str = Query(...),
    corpid: Optional[str] = Query(None),
    subid: Optional[str] = Query(None),
    uid: Optional[str] = Query(None),
    path: Optional[str] = Query(None),
    name: Optional[str] = Query(None),
    newname: Optional[str] = Query(None),
    oldname: Optional[str] = Query(None),
    newpath: Optional[str] = Query(None),
    oldpath: Optional[str] = Query(None),
    nodeid: Optional[str] = Query(None),
    admin: Optional[int] = Query(0),
    groupid: Optional[str] = Query(None),
    startnum: Optional[int] = Query(1),
    pagenum: Optional[int] = Query(200),
    type: Optional[int] = Query(5),
    order: Optional[int] = Query(1),
    ftype: Optional[int] = Query(0),
    key: Optional[str] = Query(None),
    flag: Optional[int] = Query(0)
):
    if action == "subiddir" or action == "root":
        # 获取根目录
        if not corpid or not subid or not uid:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"}) 
        
        # 根据是否为管理员构建查询SQL
        if admin == 1:
            # 管理员可以查看所有目录
            sql = "SELECT name, subid, uid, filetype, ctime FROM file_info WHERE delflag = 0 AND filetype = 0"
            params = ()
        else:
            # 普通用户只能查看公共空间和自己的目录 pid=0 
            sql = "SELECT name, subid, uid, filetype, ctime FROM file_info WHERE delflag = 0 AND filetype = 0 AND (pid=0 OR uid = ?)"
            params = (uid,)
        
        # 使用通用查询函数
        results, totalnum, error = folder_db_query(corpid, sql=sql, params=params)
        
        if error:
            return JSONResponse(content={"code": 500, "msg": error})
        
        # 格式化结果
        data = []
        for row in results:
            data.append({
                "name": row[0],
                "subid": row[1],
                "uid": row[2],
                "filetype": str(row[3]),
                "ctime": row[4]
            })
        
        return JSONResponse(content={"code": 200, "data": data})
    
    elif action == "create":
        # 创建文件夹
        if not corpid or not subid or not uid or not path or not name:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"})
        
        # 使用封装的函数创建文件夹
        result = nas_add_folder_create_v1(corpid, path, name, 0, uid, 1)  # pid设为0，filetype=1表示目录
        
        if result == 0:
            return JSONResponse(content={"code": 200})
        elif result == -1:
            return JSONResponse(content={"code": 500, "msg": "数据库连接失败"})
        elif result == -2:
            return JSONResponse(content={"code": 400, "msg": "目录已存在"})
        elif result == -3:
            return JSONResponse(content={"code": 500, "msg": "创建目录失败"})
        elif result == -4:
            return JSONResponse(content={"code": 500, "msg": "更新数据库失败"})
        else:
            return JSONResponse(content={"code": 500, "msg": "内部错误"})
    
    elif action == "rename":
        # 重命名文件夹
        if not corpid or not subid or not uid or not path or not oldname or not newname:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"})
        
        # 使用封装的函数重命名文件夹
        result = nas_rename_folder(corpid, path, oldname, newname, 0, uid)  # pid设为0
        
        if result == 0:
            return JSONResponse(content={"code": 200})
        elif result == -1:
            return JSONResponse(content={"code": 500, "msg": "数据库连接失败"})
        elif result == -2:
            return JSONResponse(content={"code": 404, "msg": "目录不存在"})
        elif result == -3:
            return JSONResponse(content={"code": 400, "msg": "新名称已存在"})
        elif result == -4:
            return JSONResponse(content={"code": 403, "msg": "无权限"})
        elif result == -5:
            return JSONResponse(content={"code": 500, "msg": "重命名目录失败"})
        elif result == -6:
            return JSONResponse(content={"code": 500, "msg": "更新数据库失败"})
        else:
            return JSONResponse(content={"code": 500, "msg": "内部错误"})
    
    elif action == "delete":
        # 删除文件夹
        if not corpid or not subid or not uid or not path or not name:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"})
        
        # 使用封装的函数删除文件夹
        result = nas_delete(corpid, name, 1)  # filetype=1表示目录
        
        if result == 0:
            return JSONResponse(content={"code": 200})
        elif result == -1:
            return JSONResponse(content={"code": 500, "msg": "数据库连接失败"})
        elif result == -2:
            return JSONResponse(content={"code": 404, "msg": "目录不存在"})
        elif result == -3:
            return JSONResponse(content={"code": 500, "msg": "删除失败"})
        else:
            return JSONResponse(content={"code": 500, "msg": "内部错误"})
    
    elif action == "move":
        # 移动文件夹
        if not corpid or not subid or not uid or not oldpath or not newpath or not name:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"})
        
        # 使用封装的函数移动文件夹
        result = nas_move_folder(corpid, oldpath, name, newpath, name, uid)
        
        if result == 0:
            return JSONResponse(content={"code": 200})
        elif result == -1:
            return JSONResponse(content={"code": 500, "msg": "数据库连接失败"})
        elif result == -2:
            return JSONResponse(content={"code": 404, "msg": "源目录不存在"})
        elif result == -3:
            return JSONResponse(content={"code": 404, "msg": "目标目录不存在"})
        elif result == -4:
            return JSONResponse(content={"code": 403, "msg": "无权限"})
        elif result == -5:
            return JSONResponse(content={"code": 400, "msg": "目标路径已存在同名目录"})
        elif result == -6:
            return JSONResponse(content={"code": 500, "msg": "移动目录失败"})
        elif result == -7:
            return JSONResponse(content={"code": 500, "msg": "更新数据库失败"})
        else:
            return JSONResponse(content={"code": 500, "msg": "内部错误"})
    
    elif action == "copy":
        # 拷贝文件夹
        if not corpid or not subid or not uid or not oldpath or not newpath or not name:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"})
        
        # 使用封装的函数拷贝文件夹
        result = nas_copy_folder(corpid, oldpath, name, newpath, name, uid)
        
        if result == 0:
            return JSONResponse(content={"code": 200})
        elif result == -1:
            return JSONResponse(content={"code": 500, "msg": "数据库连接失败"})
        elif result == -2:
            return JSONResponse(content={"code": 404, "msg": "源目录不存在"})
        elif result == -3:
            return JSONResponse(content={"code": 404, "msg": "目标目录不存在"})
        elif result == -4:
            return JSONResponse(content={"code": 403, "msg": "无权限"})
        elif result == -5:
            return JSONResponse(content={"code": 400, "msg": "目标路径已存在同名目录"})
        elif result == -6:
            return JSONResponse(content={"code": 500, "msg": "复制目录失败"})
        elif result == -7:
            return JSONResponse(content={"code": 500, "msg": "更新数据库失败"})
        else:
            return JSONResponse(content={"code": 500, "msg": "内部错误"})
    
    elif action == "getlist":
        # 获取文件列表
        if not corpid or not subid or not uid or not path:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"})
        
        # 构建排序条件
        sort_conditions = {
            1: "filetype",
            2: "ctime",
            3: "fsize",
            4: "name",
            5: "filetype, ctime",
            6: "filetype ASC, fsize",
            7: "filetype ASC, name"
        }
        
        sort_field = sort_conditions.get(type, "filetype, ctime")
        sort_order = "ASC" if order == 1 else "DESC"
        
        # 计算分页参数
        offset = (startnum - 1) * pagenum
        
        # 构建查询SQL
        query = f"""
            SELECT fid, name, codetime, fsize, nodeid, pid, subid, filetype, ctime, resolvepower, mimeid 
            FROM file_info 
            WHERE delflag = 0
            ORDER BY {sort_field} {sort_order}
            LIMIT ? OFFSET ?
        """
        
        # 总数查询SQL
        count_sql = "SELECT COUNT(*) FROM file_info WHERE delflag = 0"
        
        # 使用通用查询函数
        results, totalnum, error = folder_db_query(
            corpid, 
            path, 
            sql=query, 
            params=(pagenum, offset),
            count_sql=count_sql
        )
        
        if error:
            return JSONResponse(content={"code": 500, "msg": error})
        
        # 格式化结果
        data = []
        for row in results:
            data.append({
                "fid": row[0] or "",
                "name": row[1],
                "codetime": row[2] or "",
                "fsize": str(row[3]) if row[3] else "0",
                "nodeid": row[4] or "0",
                "pid": row[5] or "0",
                "subid": str(row[6]) if row[6] else "0",
                "filetype": str(row[7]) if row[7] else "0",
                "ctime": row[8] or "",
                "resolvepower": row[9] or "",
                "mimeid": str(row[10]) if row[10] else ""
            })
        
        return JSONResponse(content={
            "code": 200,
            "msg": "success",
            "totalnum": totalnum,
            "data": data,
            "num": len(data)
        })
    
    elif action == "getsubdir":
        # 获取文件夹下子目录
        if not corpid or not subid or not uid or not path:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"})
        
        # 构建排序条件
        sort_conditions = {
            1: "filetype",
            2: "ctime",
            3: "fsize",
            4: "name",
            5: "filetype, ctime",
            6: "filetype ASC, fsize",
            7: "filetype ASC, name"
        }
        
        sort_field = sort_conditions.get(type, "filetype, ctime")
        sort_order = "ASC" if order == 1 else "DESC"
        
        # 计算分页参数
        offset = (startnum - 1) * pagenum
        
        # 构建查询SQL（带分页）
        query = f"""
            SELECT name, filetype, ctime, subid
            FROM file_info
            WHERE filetype = 0 and delflag = 0
            ORDER BY {sort_field} {sort_order}
            LIMIT ? OFFSET ?
        """
        
        # 总数查询SQL
        count_sql = "SELECT COUNT(*) FROM file_info WHERE filetype = 0 and delflag = 0"
        
        # 使用通用查询函数
        results, totalnum, error = folder_db_query(
            corpid, 
            path, 
            sql=query, 
            params=(pagenum, offset),
            count_sql=count_sql
        )
        
        if error:
            return JSONResponse(content={"code": 500, "msg": error})
        
        # 格式化结果
        data = []
        for row in results:
            data.append({
                "name": row[0],
                "filetype": str(row[1]),
                "ctime": row[2],
                "subid": str(row[3])
            })
        
        return JSONResponse(content={
            "code": 200,
            "msg": "success",
            "totalnum": totalnum,
            "data": data,
            "num": len(data)
        })
    
    elif action == "getsubfile":
        # 获取文件夹下全部文件
        if not corpid or not subid or not uid or not path:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"})
        
        # 构建排序条件
        sort_conditions = {
            1: "filetype",
            2: "ctime",
            3: "fsize",
            4: "name",
            5: "filetype, ctime",
            6: "filetype ASC, fsize",
            7: "filetype ASC, name"
        }
        
        sort_field = sort_conditions.get(type, "filetype, ctime")
        sort_order = "ASC" if order == 1 else "DESC"
        
        # 计算分页参数
        offset = (startnum - 1) * pagenum
        
        # 构建查询SQL（带分页）
        query = f"""
            SELECT fid, name, codetime, fsize, mimeid, nodeid, pid, subid, filetype, ctime, resolvepower
            FROM file_info where filetype != 0 and delflag = 0
            ORDER BY {sort_field} {sort_order}
            LIMIT ? OFFSET ?
        """
        
        # 总数查询SQL
        count_sql = "SELECT COUNT(*) FROM file_info"
        
        # 使用通用查询函数
        results, totalnum, error = folder_db_query(
            corpid, 
            path, 
            sql=query, 
            params=(pagenum, offset),
            count_sql=count_sql
        )
        
        if error:
            return JSONResponse(content={"code": 500, "msg": error})
        
        # 格式化结果
        data = []
        for row in results:
            data.append({
                "fid": row[0],
                "name": row[1],
                "codetime": row[2] or "",
                "fsize": str(row[3]),
                "mimeid": str(row[4]) if row[4] else "0",
                "nodeid": row[5] or "0",
                "pid": row[6] or "0",
                "subid": str(row[7]),
                "filetype": str(row[8]),
                "ctime": row[9],
                "resolvepower": row[10] or ""
            })
        
        return JSONResponse(content={
            "code": 200,
            "msg": "success",
            "totalnum": totalnum,
            "data": data,
            "num": len(data)
        })
    
    elif action == "search":       
        from .nas_search import search_files_and_folders 
        result = search_files_and_folders(corpid, subid, uid, path, key, type, flag)
        return JSONResponse(content=result)
    
    else:
        return JSONResponse(content={"code": 400, "msg": "无效的操作类型"})

# 回收站操作接口

# 获取回收站列表
@folder_router.get("/v1/oss/recycle")
async def recycle_operations(
    action: str = Query(...),
    corpid: Optional[str] = Query(None),
    subid: Optional[str] = Query(None),
    uid: Optional[str] = Query(None),
    startnum: Optional[int] = Query(1),
    pagenum: Optional[int] = Query(200),
    name: Optional[str] = Query(None),
    vpath: Optional[str] = Query(None),
    nodeid: Optional[str] = Query(None),
    fid: Optional[str] = Query(None),
    type: Optional[int] = Query(1)
):
    if action == "getlist":
         # 获取文件列表
        if not corpid or not subid or not uid :
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"})        
        # 构建排序条件
        sort_conditions = {
            1: "filetype",
            2: "ctime",
            3: "fsize",
            4: "name",
            5: "filetype, ctime",
            6: "filetype ASC, fsize",
            7: "filetype ASC, name"
        }        
        sort_field = sort_conditions.get(type, "filetype, ctime")
        sort_order = "ASC" if order == 1 else "DESC"
        
        # 计算分页参数
        offset = (startnum - 1) * pagenum
        
        # 构建查询SQL
        query = f"""
            SELECT fid, name, codetime, fsize, nodeid, pid, subid, filetype, ctime, resolvepower, mimeid 
            FROM file_info 
            WHERE delflag = 0
            ORDER BY {sort_field} {sort_order}
            LIMIT ? OFFSET ?
        """
        
        # 总数查询SQL
        count_sql = "SELECT COUNT(*) FROM file_info WHERE delflag = 0"
        
        # 使用通用查询函数
        results, totalnum, error = folder_db_query(
            corpid, 
            RECYBIN_PATH , 
            sql=query, 
            params=(pagenum, offset),
            count_sql=count_sql
        )
        
        if error:
            return JSONResponse(content={"code": 500, "msg": error})
        
        # 格式化结果
        data = []
        for row in results:
            data.append({
                "fid": row[0] or "",
                "name": row[1],
                "codetime": row[2] or "",
                "fsize": str(row[3]) if row[3] else "0",
                "nodeid": row[4] or "0",
                "pid": row[5] or "0",
                "subid": str(row[6]) if row[6] else "0",
                "filetype": str(row[7]) if row[7] else "0",
                "ctime": row[8] or "",
                "resolvepower": row[9] or "",
                "mimeid": str(row[10]) if row[10] else ""
            })
        
        return JSONResponse(content={
            "code": 200,
            "msg": "success",
            "totalnum": totalnum,
            "data": data,
            "num": len(data)
        })
    
    elif action == "recover":        
        if not corpid or not subid or not uid or not name:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"}) 
        # 检查文件是否在回收站中（delflag=1）
        # 需要查询fid和vpath
        recycle_query = "SELECT fid, vpath FROM file_info WHERE corpid = ? AND subid = ? AND uid = ? AND name = ? AND delflag = 1"
        recycle_params = (corpid, subid, uid, name)
        recycle_results, _, error = folder_db_query(corpid, RECYBIN_PATH, sql=recycle_query, params=recycle_params)        
        if error:
            return JSONResponse(content={"code": 500, "msg": error})        
        if not recycle_results:
            return JSONResponse(content={"code": 404, "msg": "文件不存在于回收站"})    
        fid = recycle_results[0][0]
        vpath = recycle_results[0][1]      
        
        # 构建完整文件路径
        full_path = os.path.join(ROOTPATH, corpid, vpath)        
        # 获取父目录并检查是否存在
        file_parent_path = os.path.dirname(full_path)
        if not os.path.exists(file_parent_path):
            return JSONResponse(content={"code": 404, "msg": "源文件目录不存在"})
        
        # 初始化文件名变量
        final_name = name
        base_name, ext = os.path.splitext(name)
        counter = 1
        
        while True:
            file_check_query = "SELECT * FROM file_info WHERE fpath = ? AND name = ? AND delflag = 0"
            file_check_results, _, error = folder_db_query(
                corpid, 
                path=corpid, 
                sql=file_check_query, 
                params=(file_parent_path, final_name)
            )
            
            if error:
                return JSONResponse(content={"code": 500, "msg": error})
            
            if not file_check_results:
                break
            
            # 存在同名文件，添加后缀
            final_name = f"{base_name}_{counter}{ext}"
            counter += 1
        
        # 更新文件记录，将delflag从1改为0，并更新路径和名称
        update_file_query = """
            UPDATE file_info 
            SET delflag = 0, fpath = ?, name = ? 
            WHERE fid = ? AND corpid = ?
        """
        file_params = (file_parent_path, final_name, fid, corpid)
        _, _, error = folder_db_query(corpid, path=corpid, sql=update_file_query, params=file_params)
        
        if error:
            return JSONResponse(content={"code": 500, "msg": error}) 
        return JSONResponse(content={"code": 200, "msg": "恢复成功", "new_name": final_name if final_name != name else None})
      
    elif action == "delete":
        # 将文件标记为删除状态（delflag=1），不实际删除文件
        if not corpid or not subid or not uid or not name:
            return JSONResponse(content={"code": 400, "msg": "缺少必要参数"})   
        if fid:          
            update_query = "UPDATE file_info SET delflag = 1 WHERE fid = ?"
            update_params = (fid,)
        else:  
            update_query = "UPDATE file_info SET delflag = 1 WHERE name = ?"
            update_params = (name,)
        
        # 执行更新操作
        _, _, error = folder_db_query(corpid, ROOTPATH, sql=update_query, params=update_params)
        
        if error:
            return JSONResponse(content={"code": 500, "msg": error})
        
        return JSONResponse(content={"code": 200, "msg": "文件已标记为删除状态"})
    
    else:
        return JSONResponse(content={"code": 400, "msg": "无效的操作类型"})